package snapex.expense.service.impl;

import static org.mybatis.dynamic.sql.SqlBuilder.isEqualTo;
import static org.mybatis.dynamic.sql.SqlBuilder.isIn;
import static org.mybatis.dynamic.sql.SqlBuilder.isNotNull;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.function.Predicate;

import org.mybatis.dynamic.sql.select.MyBatis3SelectModelAdapter;
import org.mybatis.dynamic.sql.select.QueryExpressionDSL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import lombok.extern.slf4j.Slf4j;
import snapex.core.model.CurrencyRate;
import snapex.core.model.Expense;
import snapex.core.model.ExpenseCashAdvance;
import snapex.core.model.ExpenseDetail;
import snapex.core.model.ProjectCustomerInfo;
import snapex.core.model.Result;
import snapex.core.model.SearchParams;
import snapex.core.model.StaffInfo;
import snapex.core.model.UserAuthority;
import snapex.core.model.UserGroups;
import snapex.expense.mapper.CurrencyRateDynamicSqlSupport;
import snapex.expense.mapper.ExpenseDynamicSqlSupport;
import snapex.expense.mapper.HtcCurrencyRateMapper;
import snapex.expense.mapper.HtcExpenseCashAdvanceMapper;
import snapex.expense.mapper.HtcExpenseDetailMapper;
import snapex.expense.mapper.HtcExpenseMapper;
import snapex.expense.mapper.HtcProjectCustomerInfoMapper;
import snapex.expense.mapper.HtcStaffInfoMapper;
import snapex.expense.mapper.HtcUserAuthorityMapper;
import snapex.expense.mapper.HtcUserGroupsMapper;
import snapex.expense.mapper.ext.HtcExpenseSqlProvider;

@Slf4j
@Transactional
@Component
public class HtcExpenseServiceFacade {

	@Autowired
	private HtcExpenseMapper expenseMapper;
	
	@Autowired
	private HtcExpenseDetailMapper expenseDetailMapper;

	@Autowired
	private HtcExpenseCashAdvanceMapper expenseCashAdvanceMapper;
	
	@Autowired
	private HtcStaffInfoMapper staffInfoMapper;
	
	@Autowired
	private	HtcProjectCustomerInfoMapper projectCustomerInfoMapper;
	
	@Autowired
	private HtcCurrencyRateMapper currencyRateMapper;
	
	@Autowired
	private HtcUserGroupsMapper userGroupsMapper;
	
	@Autowired
	private HtcUserAuthorityMapper userAuthorityMapper;
	
	@Autowired @Qualifier("expenseJdbcTemplate")
	private JdbcTemplate expenseJdbcTemplate;
	
		
	public List<StaffInfo> getAllStaffInfo() {
		return staffInfoMapper.selectByExample().build().execute();
	}
	
	public List<ProjectCustomerInfo> getProjectCustomerInfo() {
		return projectCustomerInfoMapper.selectByExample().build().execute();
	}
	
	public List<CurrencyRate> getAllCurrencyRate(String rateMonth) {
//		Date current = new Date(System.currentTimeMillis());	
//		current.toString().substring(0, "yyyy-MM".length()));
		
		if(rateMonth != null) {
			return currencyRateMapper.selectByExample().where(CurrencyRateDynamicSqlSupport.rateMonth, isEqualTo(rateMonth)).build().execute();
		}
		
		return currencyRateMapper.selectByExample().build().execute();
	}
	
	public List<UserGroups> getUserGroups() {
		return userGroupsMapper.selectByExample().build().execute();
	}
	
	public List<UserAuthority> getUserAuthority() {
		return userAuthorityMapper.selectByExample().build().execute();
	}
	
	public Result syncExpenses(List<Expense> expenses) {
		
		log.debug("*******Start to sync expenses:{}", expenses.size());
		
		if(expenses.isEmpty()) {
			return Result.success().msg("No expense to sync");
		}
		
		Set<String> expenseIds = new HashSet<String>();
		expenses.forEach( e -> expenseIds.add(e.getExpenseId()));		
		String[] ids = expenseIds.toArray(new String[] {});
		
		//remove records which have been imported before to avoid duplicated records 
		Set<String> existingExpenseIds = new HashSet<String>();		
		expenseMapper.selectByExample().where(ExpenseDynamicSqlSupport.expenseId, isIn(ids)).build().execute().forEach(e -> existingExpenseIds.add(e.getExpenseId()));
				
		expenses.removeIf(new Predicate<Expense>() {
			@Override
			public boolean test(Expense t) {				
				return existingExpenseIds.contains(t.getExpenseId());			
			}			
		});
		
		if(expenses.isEmpty()) {
			return Result.success().msg("Records already synchronized, ignored ExpenseIds:" + existingExpenseIds.toString());
		}
		
		
		List<ExpenseDetail> purposes = new ArrayList<ExpenseDetail>();
		List<ExpenseCashAdvance> cashes = new ArrayList<ExpenseCashAdvance>();	
		expenseIds.clear();
		for (Expense expense : expenses) {
			expenseIds.add(expense.getExpenseId());
			purposes.addAll(expense.getPurposes());
			cashes.addAll(expense.getDeductions());
		}
		
		expenseJdbcTemplate.batchUpdate(HtcExpenseSqlProvider.insertExpense(), expenses, 100, new  ParameterizedPreparedStatementSetter<Expense>() {
			@Override
			public void setValues(PreparedStatement ps, Expense expense) throws SQLException {
				ps.setString(1, expense.getExpenseId());
				ps.setInt(2, expense.getStatus());
				ps.setString(3, expense.getNextApprover());
				ps.setString(4, expense.getLastApprover());
				ps.setString(5, expense.getStaffId());
				ps.setString(6, expense.getExtensionNo());
				ps.setInt(7, expense.getSiteId());
				ps.setBigDecimal(8, expense.getTotalAmount());
				ps.setBigDecimal(9, expense.getExpenseCashAdvanceAmount());
				ps.setDate(10, expense.getSubmittedDate());
				ps.setDate(11, expense.getConfirmedDate());
				ps.setString(12, expense.getClarityProjectId()==null?"":expense.getClarityProjectId());
				ps.setString(13, expense.getClarityProjectName()==null?"":expense.getClarityProjectName());
				ps.setString(14, expense.getCustomerCostCentreName()==null?"":expense.getCustomerCostCentreName());
				ps.setString(15, expense.getCustomerCostCentreNo()==null?"":expense.getCustomerCostCentreNo());				
				ps.setInt(16, expense.getBillable());				
				ps.setString(17, expense.getCustomerCostCentreMandatory()==null?"":expense.getCustomerCostCentreMandatory());		
			}			
		});
		
//		expenseJdbcTemplate.execute(ExpenseSqlProvider.deleteExpenseDetail());		
		expenseJdbcTemplate.batchUpdate(HtcExpenseSqlProvider.insertExpenseDetail(), purposes, 100, new  ParameterizedPreparedStatementSetter<ExpenseDetail>() {
			@Override
			public void setValues(PreparedStatement ps, ExpenseDetail ed) throws SQLException {
				ps.setString(1, ed.getExpenseDetailId());
				ps.setString(2, ed.getExpenseId());
				ps.setString(3, ed.getOccurDate());
				ps.setString(4, ed.getDescription());
				ps.setString(5, ed.getCurrency());
				ps.setBigDecimal(6, ed.getAmount());				
				ps.setInt(7, ed.getPurposeId());				
				ps.setString(8, ed.getId());				
				ps.setInt(9, ed.getListIndex());
			}			
		});
		
		expenseJdbcTemplate.batchUpdate(HtcExpenseSqlProvider.insertExpenseMail(), expenses, 100, new  ParameterizedPreparedStatementSetter<Expense>() {
			@Override
			public void setValues(PreparedStatement ps, Expense expense) throws SQLException {
				ps.setString(1, expense.getExpenseId());
				ps.setInt(2, expense.getStatus());				
			}			
		});
		
		StringBuilder msg = new StringBuilder();
		if(!existingExpenseIds.isEmpty()) {
			msg.append("Exepenses already exist, ignored ExpenseIds:").append(existingExpenseIds.toString()).append(";");			
		}
		msg.append("Number of exepenses updated:" ).append(expenseIds.size()).append(", Expense ids:").append(expenseIds.toString());
		return Result.success().msg(msg.toString());
	}
		
	public List<Expense> getExpenses(SearchParams params) {
		
		return  expensesQueryBuilder(params).build().execute();
	}
		
	private QueryExpressionDSL<MyBatis3SelectModelAdapter<List<Expense>>>.QueryExpressionWhereBuilder expensesQueryBuilder(SearchParams params) {

		QueryExpressionDSL<MyBatis3SelectModelAdapter<List<Expense>>>.QueryExpressionWhereBuilder queryBuilder = expenseMapper
				.selectByExample().where(ExpenseDynamicSqlSupport.expenseId, isNotNull());

		if (params.getExpenseId() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.expenseId, isEqualTo(params.getExpenseId()));
		}

		if (params.getStatus() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.status, isEqualTo(params.getStatus()));
		}

		if(params.getSubmittedDate() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.submittedDate, isEqualTo(params.getSubmittedDate()));
		}
		
		if(params.getProjectId() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.clarityProjectId, isEqualTo(params.getProjectId()));
		}
		
		if(params.getStaffId() != null) {
			queryBuilder.and(ExpenseDynamicSqlSupport.staffId, isEqualTo(params.getStaffId()));
		}
		
		return queryBuilder;
	}
}
